﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MediaLibraryManagement.Entity;

namespace MediaLibraryManagement.DAL
{
    public class SongDAL : BaseDAL
    {
        #region Properties Implementation

        protected override string TableName { get { return DatabaseTableName.Songs; } }

        #endregion

        public List<Song> GetByAlbumID(int albumID)
        {
            List<Song> songs = new List<Song>();
            try
            {
                OpenConnection();
                string commandText = string.Format("SELECT * FROM {0} WHERE AlbumID = {1}", TableName, albumID);
                using (SqlCommand command = new SqlCommand(commandText, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Song cast = (Song)Fetch(reader);
                            songs.Add(cast);
                        }
                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return songs;
        }

        public void Insert(List<Song> songs)
        {
            try
            {
                OpenConnection();
                string commandstring = string.Format("INSERT INTO {0} (AlbumID, Title, Length) "
                                                            + "VALUES (@AlbumID, @Title, @Length)"
                                                    , TableName);
                using (SqlCommand command = new SqlCommand(commandstring, Connection))
                {
                    command.Parameters.Add("@AlbumID", SqlDbType.Int);
                    command.Parameters.Add("@Title", SqlDbType.NVarChar);
                    command.Parameters.Add("@Length", SqlDbType.NVarChar);
                    foreach (Song song in songs)
                    {
                        command.Parameters["@AlbumID"].Value = song.AlbumID;
                        command.Parameters["@Title"].Value = song.Title;
                        command.Parameters["@Length"].Value = song.Length;
                        command.ExecuteNonQuery();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public void DeleteByAlbumID(int albumID)
        {
            try
            {
                OpenConnection();
                string commandText = string.Format("DELETE FROM {0} WHERE AlbumID = {1}", TableName, albumID);
                using (SqlCommand command = new SqlCommand(commandText, Connection))
                {
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        #region Private Methods

        protected override IEntity Fetch(IDataReader reader)
        {
            Song song = new Song();
            song.ID = (Int32)reader["ID"];
            song.AlbumID = (Int32)reader["AlbumID"];
            song.Title = reader["Title"].ToString();
            //song.Genre = reader["Genre"].ToString();
            song.Length = reader["Length"].ToString();
            //song.Composer = reader["Composer"].ToString();
            //song.Lyrics = reader["Lyrics"].ToString();
            //song.Rate = float.Parse(reader["Rate"].ToString());
            //song.TrackNumber = (Int32)reader["TrackNumber"];
            song.URL = reader["URL"].ToString();
            return song;
        }

        #endregion
    }
}